package com.wms.service;

import com.mes.entity.User;
import com.yy.base.common.dao.Dao;
import com.yy.base.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;


@Service
public class LastBottomPairService {
	@Autowired
	private Dao dao;

	//加载所有的作业工单信息
	public List<Map<String,Object>> getJobOrderList() {
		return dao.query("SELECT  " +
				"  job.emeso_job_order_id AS PLANID,  " +
				"  job.CODE AS JOBCODE,  " +
				"  wl.name AS XTNAME,  " +
				"  (select wm_concat(distinct p.value) from m_product p, emeso_job_plan s " +
				"	where p.m_product_id = s.xuantou_id and s.emeso_manuorder_id = job.emeso_manuorder_id) xuancode,  " +
				"  (select wm_concat(distinct s.xuantou_type) from emeso_job_plan s where s.emeso_manuorder_id = job.emeso_manuorder_id) xuantype,  " +
				"  (select s.foot_size_code from emeso_manuorder s where s.emeso_manuorder_id = job.emeso_manuorder_id) cmcode,  " +
				"  job.Emeso_Manuorder_ID AS SCID,  " +
				"  (case when job.pair = 'Y' then '已配对' else '未配对' end) status,  " +
				"  (SELECT x.name from emes_xuantou x, emes_pbom p   " +
				"    where x.emes_xuantou_id = p.emes_xuantou_id   " +
				"      and p.m_product_id = job.m_product_id) XUANTOU,  " +
				"  (select distinct p.name from emes_component c, m_product p, emeso_manuorder_bom bom   " +
				"    where p.m_product_id = bom.m_product_id   " +
				"      and c.emes_component_id = bom.emes_component_id  " +
				"   and bom.emeso_manuorder_id = job.emeso_manuorder_id" +
				"      and c.code = 'SC-XT-02' limit 1) dd,  " +
				"  (select distinct p.value from emes_component c, m_product p, emeso_manuorder_bom bom   " +
				"    where p.m_product_id = bom.m_product_id   " +
				"      and c.emes_component_id = bom.emes_component_id  " +
				"   and bom.emeso_manuorder_id = job.emeso_manuorder_id" +
				"      and c.code = 'SC-XT-02' limit 1) ddcode,  " +
				"  job.description AS REMARKS  " +
				" FROM m_product wl, emeso_job_order job  " +
				"where job.m_product_id = wl.m_product_id");
	}


	//加载该作业订单对应的配对信息(作业工单所绑定的楦头、大底信息)
	public List<Map<String,Object>> getJobOrderPairLists(String code) {

		return dao.query(" SELECT      " +
				"        job.emeso_job_order_id as PLANID  ,  " +
				"         job.CODE AS JOBCODE, wl.VALUE     AS WLCODE,  wl.m_product_id as WLID ,     " +
				"         job.description AS REMARKS,      " +
				"         job.Emeso_Manuorder_ID AS SCID ,      " +
				"         zy.type  , zy.xuantou_id as SHOEID    , " +
				"          zy.emes_size_id as CMID  " +
				"    FROM      " +
				"         emeso_job_order job,    " +
				"         m_product wl ,   " +
				"         emeso_job_plan zy      " +
				"    WHERE  job.m_product_id = wl.m_product_id   AND" +
				"  job.emeso_manuorder_id = zy.emeso_manuorder_id      " +
				"         AND job.CODE = '"+code+"' " );
	}


	//加载选择作业工单对应的生产订单信息及大底信息
	public List<Map<String,Object>> getBatchnoSoleLists(String code) {

		return dao.query("SELECT  " +
				"  job.emeso_job_order_id AS PLANID,  " +
				"  job.CODE AS JOBCODE,  " +
				"  wl.value AS WLCODE,  " +
				"  wl.name AS XTNAME,  " +
				"        jp.type  , jp.xuantou_id as SHOEID    ,  " +
				"         jp.emes_size_id as CMID  , " +
				"  job.Emeso_Manuorder_ID AS SCID,  " +
				"  (SELECT x.name from emes_xuantou x, emes_pbom p   " +
				"    where x.emes_xuantou_id = p.emes_xuantou_id   " +
				"      and p.m_product_id = job.m_product_id) XUANTOU,  " +
				"  (select distinct p.value from emes_component c, m_product p, emeso_manuorder_bom bom   " +
				"    where p.m_product_id = bom.m_product_id   " +
				"      and c.emes_component_id = bom.emes_component_id  " +
				"      and c.code = 'SC-XT-02' limit 1) SOLEWLID,  " +
				"  job.description AS REMARKS  " +
				" FROM emeso_job_plan jp, m_product wl, emeso_job_order job  " +
				" where jp.emeso_manuorder_id = job.emeso_manuorder_id" +
				"	and job.m_product_id = wl.m_product_id " +
				" and job.code = '"+code+"'" );
	}

	//根据传过来的RFID号带出楦头、大底的基础信息
	public List<Map<String,Object>> getBatchnorLists(String batch_no) {

		return dao.query("SELECT  " +
				"    mx.wms_storage_batch_item_id as ITEMID, pc.wms_storage_batch_id as BATCHID,  pc.c_uom_id as DWID,   " +
				"  wl.m_product_id as WLCODE, wl.value as WLVALUE,  " +
				"  pc.emes_size_id as CMID , mx.status as STATUS " +
				"FROM  " +
				"  wms_storage_batch pc,  " +
				"  m_product wl ,  " +
				"  wms_storage_batch_item mx  " +
				"WHERE  " +
				"  pc.m_product_id = wl.m_product_id  " +
				"  and pc.wms_storage_batch_id = mx.wms_storage_batch_id  " +
				"  and mx.code = '"+batch_no+"'  " +
				"  group by   wl.m_product_id ,   pc.emes_size_id  ,mx.status ,mx.wms_storage_batch_item_id,pc.wms_storage_batch_id,  pc.c_uom_id ");
	}

	//根据传过来的楦头编码找到时间最近的作业工单及工单匹配的大底
	public List<Map<String,Object>> getProductDdCode(String rfid) {
		return dao.query(" SELECT  " +
				"  zy.code as ZYCODE,zy.emeso_job_order_id as PLANID, bom.m_product_id as WLID,batch.emes_size_id as SIZEID , wl.value as WLVALUE,zy.description as REMARKS ," +
				"  (select value from m_product where m_product_id = batch.m_product_id ) as XTVALUE , batch.m_product_id as XTID , item.foot_type as FOOTTYPE ,item.wms_storage_batch_item_id as ITEMID " +
				" FROM  " +
				"  wms_storage_batch_item item,  " +
				"  wms_storage_batch batch ,  " +
				"  Emeso_Job_Plan zyj,  " +
				"  emeso_job_order zy,  " +
				"  emeso_manuorder_bom bom,  " +
				"  emes_component bw ," +
				"  m_product wl  " +
				" WHERE  " +
				"  batch.wms_storage_batch_id = item.wms_storage_batch_id  " +
				"  and batch.m_product_id = zyj.xuantou_id  " +
				"  and batch.emes_size_id =zyj.emes_size_id  " +
				"  and zyj.emeso_manuorder_id = zy.emeso_manuorder_id  " +
				"  and zyj.emeso_manuorder_id = bom.emeso_manuorder_id  " +
				"  and bom.emes_component_id = bw.emes_component_id " +
				"  and zy.m_product_id  = wl.m_product_id " +
				"  and bw.code  = 'SC-XT-02'  " +
				"  and zy.pair != 'Y' " +
				"  and item.code = '"+rfid+"'  " +
				"  ORDER BY zy.created asc  " +
				"  LIMIT 1");
	}

	//根据传过来的大底物料ID及尺码找到所匹配的大底RFID
	public List<Map<String,Object>> getRfidDdCode(String WLID,String SIZEID) {
		return dao.query("  SELECT  " +
				" up.CODE AS DDRFID," +
				" up.rfid_left AS LEFTRFID," +
				" up.rfid_right AS RIGHTRFID, " +
				" up.left_item AS LEFTITEMID, " +
				" up.right_item AS RIGHTITEMID, " +
				" up.m_product_id AS WLID, " +
				" wl.VALUE AS WLVALUE, " +
				" wl.NAME AS WLNAME " +
				" FROM  " +
				" wms_storage_batch_up up , m_product wl    " +
				" WHERE up.m_product_id = wl.m_product_id   " +
				" and up.status = 'CREATE' " +
				" and up.m_product_id = '"+WLID+"'  " +
				" AND up.emes_size_id = '"+SIZEID+"' ");
	}


	//查询CREATEBY和UPDATEBY的ID值
	public List<Map<String,Object>> getEquipmentputCreateby(String userIdtime){
		return dao.query("select CREATEDBY as CREATEDBY, UPDATEDBY as UPDATEDBY from ad_user where ad_user_id = ?",userIdtime);
	}


	//修改库存中楦头及大底的状态
	@SuppressWarnings("unchecked")
	public boolean updateStorageBatchItem(String id) {
		boolean result = false;

		String Status= "OK";

		String sql =  "update  Wms_Storage_Batch_Item set Status = ? where Wms_Storage_Batch_Item_id = ? ";

	    result = dao.executeSql(sql,Status,id) > 0;


		return result;
	}

	//修改库存中wms_storage_batch_up的大底使用状态
	@SuppressWarnings("unchecked")
	public boolean updateStorageBatchUpStatus(String id) {
		boolean result = false;

		String Status= "OK";

		String sql =  "update  wms_storage_batch_up set Status = ? where wms_storage_batch_up_id = ? ";

		result = dao.executeSql(sql,Status,id) > 0;


		return result;
	}


	//修改作业工单状态及绑定对应的楦头及大底信息
	@SuppressWarnings("unchecked")
	public boolean updateJobPlanOrder(String leftShoeID,String rightShoeID,String leftsoleID,String rightsoleID,String planId) {
		boolean result = false;

		String Status= "OK";

		String sql =  "update  Emeso_Job_Order set left_dadi_id = ? , right_dadi_id = ? ,  left_xuantou_id = ? , right_xuantou_id = ? , pair = ? where emeso_job_order_id = ? ";

		result = dao.executeSql(sql,leftsoleID,rightsoleID,leftShoeID,rightShoeID, 'Y', planId) > 0;


		return result;
	}

	//查询鞋面辅料MES参数
	public List<Map<String,Object>> selectMesParameter(String code) {
		String sql = " select cs.value as CSVALUE from Emes_Parameter cs where cs.code = ?  " ;

		return dao.query(sql , code);
	}

	//查询鞋面辅料MES参数
	public List<Map<String,Object>> selectPlanCode(String ID) {
		String sql = " select  code as JOBCODE from emeso_job_order  where emeso_job_order_id = ?  " ;

		return dao.query(sql , ID);
	}


	/**
	 * 往inout表中插入成品信息
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public boolean inStorageInOut(String warehouseID, String batchId, String locatorID, int qty, String uomID, String jobid, User user, String jobcode) {
		boolean result = false;
		String uuids = StringUtil.getUUID();

		String sql = "insert into wms_storage_in_out "
				+ " (Wms_Storage_In_Out_ID,Wms_Storage_Batch_ID,M_Warehouse_ID,M_Locator_ID,Qty,C_Uom_ID,Opt_Mode,Opt_Code,Data_Id,Status,"
				+ "AD_CLIENT_ID, AD_ORG_ID,ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY)" +
				" values(?,?,?,?,?,?,?,?,?,'OK',?,?,?,now(),?,now(),?)";


		result =  dao.executeSql(sql, uuids,batchId,warehouseID, locatorID,qty,uomID, "楦头退料入库",jobcode,jobid, user.getClientId(), user.getOrgId(), "Y", user.getId(), user.getId()) > 0;

		return result;
	}

}
